﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_service_sel_hierup]
	@SERVICE_ID int
AS
BEGIN
	 -- WALK UP the tree.
	 WITH nested_Up AS
	(
		SELECT A.[SERVICE_ID], A.[SERVICE_NAME], A.[PARENT_ID], B.[SERVICE_TYPE], B.[SERVICE_TYPE_NAME]
		  FROM flcr.[SERVICE] A
		 INNER JOIN flcr.[SERVICE_TYPE] B
		    ON A.[SERVICE_TYPE] = B.[SERVICE_TYPE] 
		WHERE A.[SERVICE_ID] = @SERVICE_ID

		UNION ALL

		SELECT B.[SERVICE_ID], B.[SERVICE_NAME], B.[PARENT_ID], B.[SERVICE_TYPE], C.[SERVICE_TYPE_NAME] 
		  FROM nested_Up a
		 INNER JOIN flcr.[SERVICE] B
			ON A.[PARENT_ID] = B.[SERVICE_ID]
		 INNER JOIN flcr.[SERVICE_TYPE] C
		    ON B.[SERVICE_TYPE] = C.[SERVICE_TYPE] 
	)
	SELECT * FROM nested_Up a

END